﻿//****************************************************************************//
//
// @File: Queries.cs
// @Owner: lyudmilf
// @Test: 
//
// Purpose:
//
//      SQL Queries used for analysis
//
// Notes:
//      
//      
//
//****************************************************************************//

using System;

namespace SSAT.ATSDriver
{
    public static class Queries
    {
        public const string LastRbEntry = @"Select MAX(CONVERT(xml, record).value('(/Record/@id)[1]','int')) from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR'";
        public const string FilePath = @"use master
    select filename from sys.sysfiles where name = 'mastlog'";
        public const string Cleanup = @"use master;
    if exists(select * from sys.dm_xe_sessions where name='ats_session_{0}') alter event session [ats_session_{0}] on server state = stop;
    if exists(select * from sys.server_event_sessions where name='ats_session_{0}') drop event session [ats_session_{0}] on server;";
        public const string CreateSession = @"create event session [ats_session_{1}] on server
                    add event sqlserver.error_reported
                    (action (sqlserver.database_id, sqlserver.sql_text, sqlserver.database_context, sqlserver.client_app_name, sqlserver.client_pid,sqlserver.client_hostname, sqlserver.nt_username,
                     sqlserver.session_nt_username, sqlserver.username, sqlserver.session_id, package0.collect_system_time)
                     where error = 18451 or error = 18452 or error = 18456 or error = 18458 or error = 18459 or error = 18460 or error = 18461 or error = 18462 or error = 18463 or error = 18464 or error = 18465 or error = 18466 or error = 18467 or error = 18468 or error = 18470 or error = 18471 or error = 18486 or error = 18487 or error = 18488
                    )
                    add target package0.asynchronous_file_target (set filename = '{0}\ATS_{1}.xel', metadatafile = '{0}\ATS_{1}.xem') with (startup_state = off)";
        public const string StartSession = @"if not exists(select * from sys.dm_xe_sessions where name='ats_session_{0}') alter event session [ats_session_{0}] on server state = start";
        public const string Wait = @"while (select count(*) from sys.fn_xe_file_target_read_file('{0}\ATS_{1}*.xel', '{0}\ATS_{1}*.xem', null, null)) = 0
                                        begin
	                                        WAITFOR DELAY '00:00:01' 
                                        end";
        public const string Analyse = @"if exists(select * from sys.dm_xe_sessions where name='ats_session_{1}') alter event session [ats_session_{1}] on server state = stop;
    select DISTINCT f.object_name as event, CONVERT(xml, f.event_data).value('(/event/data/value)[1]','int') as 'err_number', 
	CONVERT(xml, f.event_data).value('(/event/data/value)[3]','int') as 'err_state', CONVERT(xml, f.event_data).value('(/event/data/value)[5]','nvarchar(255)') as 'err_message',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''database_id'']/value)[1]','int') as 'DbId',CONVERT(xml, f.event_data).value('(/event/action[@name = ''sql_text'']/value)[1]','nvarchar(900)') as 'sql_text',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''database_context'']/value)[1]','nvarchar(900)') as 'db_context', CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_app_name'']/value)[1]','nvarchar(255)') as 'client_app_name',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_pid'']/value)[1]','int') as 'client_pid',CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_hostname'']/value)[1]','nvarchar(255)') as 'client_hostname',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''nt_username'']/value)[1]','nvarchar(255)') as 'nt_username',CONVERT(xml, f.event_data).value('(/event/action[@name = ''session_nt_username'']/value)[1]','nvarchar(255)') as 'session_nt_username',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''username'']/value)[1]','nvarchar(255)') as 'username',CONVERT(xml, f.event_data).value('(/event/action[@name = ''session_id'']/value)[1]','int') as 'session_id',
	'RB_Error' = cast((Select CONVERT(xml, rb1.record).value('(/Record/Error/SPID)[1]','int') as 'SPID', CONVERT(xml, rb1.record).value('(/Record/Error/APIName)[1]','nvarchar(255)')as 'APIName',
	CONVERT(xml, rb1.record).value('(/Record/Error/CallingAPIName)[1]','nvarchar(255)')as 'CallingAPIName', CONVERT(xml, rb1.record).value('(/Record/Error/ErrorCode)[1]','varchar(255)')as 'ErrorCode'
    from sys.dm_os_ring_buffers rb1	where rb1.ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR' and CONVERT(xml, rb1.record).value('(/Record/@id)[1]','int') > {2}
	FOR XML RAW, ROOT) as nvarchar(max)),
    db_name(CONVERT(xml, f.event_data).value('(/event/action[@name = ''database_id'']/value)[1]','int')) as 'Db_name',
    CONVERT(xml, f.event_data).value('(/event/@timestamp)[1]','varchar(125)') as 'err_time'
    from sys.fn_xe_file_target_read_file('{0}\ATS_{1}*.xel', '{0}\ATS_{1}*.xem', null, null) f 
    order by 'err_number', 'err_state', 'err_message' desc";
        public const string GetMonitor = @"if exists(select * from sys.dm_xe_sessions where name='ats_session_{1}') alter event session [ats_session_{1}] on server state = stop;
    select count(*)
    from sys.fn_xe_file_target_read_file('{0}\ATS_{1}*.xel', '{0}\ATS_{1}*.xem', null, null) f 
    where Right(CONVERT(xml, f.event_data).value('(/event/data/value)[5]','nvarchar(255)'),1) = ']'";
        public const string GetErrorsByType = @"WITH tEvents (err_number, err_state,err_message, DbId, client_app_name, client_pid, client_hostname, nt_username,session_nt_username, username) AS
    (select CONVERT(xml, f.event_data).value('(/event/data/value)[1]','int') as 'err_number', 
	CONVERT(xml, f.event_data).value('(/event/data/value)[3]','int') as 'err_state', CONVERT(xml, f.event_data).value('(/event/data/value)[5]','nvarchar(255)') as 'err_message',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''database_id'']/value)[1]','int') as 'DbId',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_app_name'']/value)[1]','nvarchar(255)') as 'client_app_name',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_pid'']/value)[1]','int') as 'client_pid',CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_hostname'']/value)[1]','nvarchar(255)') as 'client_hostname',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''nt_username'']/value)[1]','nvarchar(255)') as 'nt_username',CONVERT(xml, f.event_data).value('(/event/action[@name = ''session_nt_username'']/value)[1]','nvarchar(255)') as 'session_nt_username',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''username'']/value)[1]','nvarchar(255)') as 'username'
    from sys.fn_xe_file_target_read_file('{0}\ATS_{1}*.xel', '{0}\ATS_{1}*.xem', null, null) f 
    where Right(CONVERT(xml, f.event_data).value('(/event/data/value)[5]','nvarchar(255)'),1) = ']')
    Select err_number , err_state, count (*) from tEvents
    group by err_number , err_state";
        public const string GetErrorsByApplication = @"WITH tEvents (err_number, err_state,err_message, DbId, client_app_name, client_pid, client_hostname, nt_username,session_nt_username, username) AS
    (select CONVERT(xml, f.event_data).value('(/event/data/value)[1]','int') as 'err_number', 
	CONVERT(xml, f.event_data).value('(/event/data/value)[3]','int') as 'err_state', CONVERT(xml, f.event_data).value('(/event/data/value)[5]','nvarchar(255)') as 'err_message',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''database_id'']/value)[1]','int') as 'DbId',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_app_name'']/value)[1]','nvarchar(255)') as 'client_app_name',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_pid'']/value)[1]','int') as 'client_pid',CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_hostname'']/value)[1]','nvarchar(255)') as 'client_hostname',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''nt_username'']/value)[1]','nvarchar(255)') as 'nt_username',CONVERT(xml, f.event_data).value('(/event/action[@name = ''session_nt_username'']/value)[1]','nvarchar(255)') as 'session_nt_username',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''username'']/value)[1]','nvarchar(255)') as 'username'
    from sys.fn_xe_file_target_read_file('{0}\ATS_{1}*.xel', '{0}\ATS_{1}*.xem', null, null) f 
    where Right(CONVERT(xml, f.event_data).value('(/event/data/value)[5]','nvarchar(255)'),1) = ']')
    Select client_app_name, count (*) from tEvents
    group by client_app_name";
        public const string GetErrorsByClientHost = @"WITH tEvents (err_number, err_state,err_message, DbId, client_app_name, client_pid, client_hostname, nt_username,session_nt_username, username) AS
    (select CONVERT(xml, f.event_data).value('(/event/data/value)[1]','int') as 'err_number', 
	CONVERT(xml, f.event_data).value('(/event/data/value)[3]','int') as 'err_state', CONVERT(xml, f.event_data).value('(/event/data/value)[5]','nvarchar(255)') as 'err_message',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''database_id'']/value)[1]','int') as 'DbId',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_app_name'']/value)[1]','nvarchar(255)') as 'client_app_name',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_pid'']/value)[1]','int') as 'client_pid',CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_hostname'']/value)[1]','nvarchar(255)') as 'client_hostname',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''nt_username'']/value)[1]','nvarchar(255)') as 'nt_username',CONVERT(xml, f.event_data).value('(/event/action[@name = ''session_nt_username'']/value)[1]','nvarchar(255)') as 'session_nt_username',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''username'']/value)[1]','nvarchar(255)') as 'username'
    from sys.fn_xe_file_target_read_file('{0}\ATS_{1}*.xel', '{0}\ATS_{1}*.xem', null, null) f 
    where Right(CONVERT(xml, f.event_data).value('(/event/data/value)[5]','nvarchar(255)'),1) = ']')
    Select client_hostname, count (*) from tEvents
    group by client_hostname";
        public const string GetErrorsByUser = @"WITH tEvents (err_number, err_state,err_message, DbId, client_app_name, client_pid, client_hostname, nt_username,session_nt_username, username) AS
    (select CONVERT(xml, f.event_data).value('(/event/data/value)[1]','int') as 'err_number', 
	CONVERT(xml, f.event_data).value('(/event/data/value)[3]','int') as 'err_state', CONVERT(xml, f.event_data).value('(/event/data/value)[5]','nvarchar(255)') as 'err_message',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''database_id'']/value)[1]','int') as 'DbId',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_app_name'']/value)[1]','nvarchar(255)') as 'client_app_name',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_pid'']/value)[1]','int') as 'client_pid',CONVERT(xml, f.event_data).value('(/event/action[@name = ''client_hostname'']/value)[1]','nvarchar(255)') as 'client_hostname',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''nt_username'']/value)[1]','nvarchar(255)') as 'nt_username',CONVERT(xml, f.event_data).value('(/event/action[@name = ''session_nt_username'']/value)[1]','nvarchar(255)') as 'session_nt_username',
	CONVERT(xml, f.event_data).value('(/event/action[@name = ''username'']/value)[1]','nvarchar(255)') as 'username'
    from sys.fn_xe_file_target_read_file('{0}\ATS_{1}*.xel', '{0}\ATS_{1}*.xem', null, null) f 
    where Right(CONVERT(xml, f.event_data).value('(/event/data/value)[5]','nvarchar(255)'),1) = ']')
    Select coalesce(username, session_nt_username), count (*) from tEvents
    group by coalesce(username, session_nt_username)";
        public const string PostCleanup = @"use master;
    if exists(select * from sys.dm_xe_sessions where name='ats_session_{0}') alter event session [ats_session_{0}] on server state = stop;
    if exists(select * from sys.server_event_sessions where name='ats_session_{0}') drop event session [ats_session_{0}] on server;";
        public const string SelectFromServerPrincipals = @"Select name from sys.server_principals where type = 'S'";
        public const string SelectSOUNDEXofPrincipals = @"Select name from sys.server_principals where SOUNDEX(name)= SOUNDEX({0}) and type = 'S'";
        public const string SelectDefaultDb = @"Select default_database_name from sys.server_principals where name = {0}";

        public const string ConnectPerms = @"select sperms.class_desc, sperms.state, 'endpoint' = (Select se.name from sys.endpoints se where sperms.major_id = se.endpoint_id), 
    'grantor' = (select spr1.name from sys.server_principals spr1 where spr1.principal_id = sperms.grantor_principal_id) 
    from sys.server_permissions sperms, sys.server_principals spr
    where spr.name = {0} and sperms.type in ('CO','COSQ') and sperms.grantee_principal_id = spr.principal_id";
        public const string GetLoginToken = @"declare @tmp_lgns table(account_name varchar(255), type varchar(255), privilege varchar(255), [mapped login name] varchar(255), [permission path] varchar(255))
    if(CHARINDEX('\',{0}) != 0) 
    begin
        insert @tmp_lgns
        exec xp_logininfo {0}, 'all'
    end
    Select t.group_name from
        (Select sp.name as name, sp1.sid as sid, sp1.name as group_name from sys.server_role_members srm join sys.server_principals sp
            on srm.member_principal_id = sp.principal_id 
            join sys.server_principals sp1 on srm.role_principal_id = sp1.principal_id
        UNION
        Select srm.account_name as name, sp.sid as sid, sp.name as group_name from @tmp_lgns srm join sys.server_principals sp on srm.[permission path] = sp.name
        UNION
        Select sp.name as name, sp.sid as sid, sp.name as group_name from sys.server_principals sp) t
    where t.name = {0} or t.name = 'public'";
        public const string GetLoginTokenSid = @"declare @tmp_lgns table(account_name varchar(255), type varchar(255), privilege varchar(255), [mapped login name] varchar(255), [permission path] varchar(255))
    if(CHARINDEX('\',{0}) != 0) 
    begin
        insert @tmp_lgns
        exec xp_logininfo {0}, 'all'
    end
    Select convert(nvarchar(255),t.sid,1) from
        (Select sp.name as name, sp1.sid as sid, sp1.name as group_name from sys.server_role_members srm join sys.server_principals sp
            on srm.member_principal_id = sp.principal_id 
            join sys.server_principals sp1 on srm.role_principal_id = sp1.principal_id
        UNION
        Select srm.account_name as name, sp.sid as sid, sp.name as group_name from @tmp_lgns srm join sys.server_principals sp on srm.[permission path] = sp.name
        UNION
        Select sp.name as name, sp.sid as sid, sp.name as group_name from sys.server_principals sp) t
    where t.name = {0} or t.name = 'public'";
        public const string DbsStatus = @"select name,is_cleanly_shutdown, state_desc,log_reuse_wait_desc, user_access_desc, mirroring_partner_instance, mirroring_partner_name , mirroring_role_desc
    from sys.databases db left join sys.database_mirroring dbm on db.name = db_name(dbm.database_id)
    where name = '{0}'";
        public const string DbsExist = @"select name from sys.databases";
        public const string DbsOffline = @"select name, state_desc,log_reuse_wait_desc from sys.databases where state <> 0 OR log_reuse_wait <> 0";
        public const string DbsAccessMode = @"select name,user_access_desc from sys.databases where user_access <> 0";
        public const string DbsMirror = @"select db_name(database_id) from sys.database_mirroring where mirroring_role = 2";
        public const string Users = @"use {0}
    select convert(nvarchar(255),dp.sid,1), dp.name, dp.principal_id, user_name(dperms.grantor_principal_id), dperms.state from sys.database_principals dp left join 
    (select * from sys.database_permissions dperms1 where dperms1.type = 'CO') dperms on dp.principal_id = dperms.grantee_principal_id
    use master";
        public const string SelectSOUNDEXofDatabases = @"Select name from sys.databases where SOUNDEX(name)= SOUNDEX({0})";
        public const string GuestCoPerm = @"use {0};
    select * from sys.database_permissions
    where type = 'CO' and grantee_principal_id = 2 and state = 'G';
    use master";
        public const string ConnectSqlRevoked = @"use master
    select sp.name from sys.server_principals sp
    where sp.name = {0} and sp.principal_id NOT IN (Select spr.grantee_principal_id from sys.server_permissions spr where spr.type = 'COSQ')";
        public const string WindowsGroupsDeniedPermission = @"use master
    select sp.name from sys.server_principals sp, sys.server_permissions spr
    where sp.principal_id = spr.grantee_principal_id and sp.type = 'G' and spr.type = 'COSQ' and spr.state = 'D'";
    }
}